import hashlib
import json

from flask import Flask, request
from sqlalchemy import create_engine, Column, Integer, String, Table, select, Select, or_, func
from sqlalchemy.orm import sessionmaker, scoped_session, declarative_base


# 创建一个引擎，目的是连接到我们的数据库上
engine = create_engine('mysql+pymysql://admin1:123@192.168.1.129:3306/mumushouji',echo=True)
# 打开数据库的连接会话
session = sessionmaker(engine)
# 保证线程安全
db_session = scoped_session(session)
# 获取基类
Base = declarative_base()


class User(Base):
    # 表结构的反射加载
    __table__ = Table("user",Base.metadata,autoload_with=engine)
# session.execute(select(User).filter_by(name="sandy")).scalar_one()

r = db_session.query(User).filter_by(user_id=1).first()
print(r.username)
# sql = select(User).filter_by(user_id=2)
sql = Select(User).filter_by(user_id=2)
print(sql)
r = db_session.execute(sql).scalar_one()
print(r.username)

# 或
result = db_session.query(User).filter(or_(User.user_id==1,User.user_id==2)).all()

result = db_session.query(User).limit(4).all()

result = db_session.query(User).limit(4).offset(2).all()

result = db_session.query(User).filter(User.user_id < 10).all()

# order by
result = db_session.query(User).order_by(User.user_id.desc()).all()

# like
result = db_session.query(User).filter(User.nickname.like('%子%'))
for r in result:
    print(r.username,r.nickname)
# sum
result = db_session.query(func.sum(User.user_id)).first()

print(result[0])
